package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.entity.investigate.BpInvestigatePlan;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.time.LocalDate;
import java.util.List;

/**
 * @Author ZhangDong
 * @Date 2020/3/25 16:24
 */
public interface BpInvestigatePlanRepository extends JpaRepository<BpInvestigatePlan, Long>, JpaSpecificationExecutor<BpInvestigatePlan> {


    int countByTypeId(Long typeId);

    int countByCheckPlanName(String checkPlanName);

    @Query("SELECT p FROM BpInvestigatePlan p WHERE p.executor LIKE CONCAT('%',:userId,'%')")
    List<BpInvestigatePlan> findExecutorLike(@Param("userId") String userId);

    @Query(value = "select p.* from wwyt_bp_investigate_plan p where p.executor like CONCAT('%',?1,'%') and p.delete_flag='0'" +
            " and not exists (select r.* from wwyt_bp_investigate_record r where r.check_plan_id=p.id and r.record_user_id=?1 and " +
            "r.check_date=?2 and r.delete_flag='0')",nativeQuery = true)
    List<BpInvestigatePlan> findCheckPlan(Long recordUserId, LocalDate checkDate);

    List<BpInvestigatePlan> findByInitiateStatusAndSourceType(String initiateStatus,Integer sourceType);

    @Query(value = "select plan.* " +
            "from wwyt_bp_investigate_plan plan " +
            "         inner join wwyt_risk_hidden_danger_content hidden " +
            "                    on plan.hidden_danger_content_id = hidden.id and hidden.delete_flag = '0' and " +
            "                       plan.delete_flag = '0' and plan.source_type = 1 and plan.initiate_status = '启用' " +
            "                        and (plan.period_days != 0 or plan.period_days is null) " +
            "         inner join wwyt_risk_point_business_evaluation_detail detail " +
            "                    on hidden.detail_id = detail.id and detail.delete_flag = '0' " +
            "         inner join wwyt_risk_point point on detail.risk_point_id = point.ID and point.DELETE_FLAG = '0'",nativeQuery=true)
    List<BpInvestigatePlan> findNewPlanToProduce();

    List<BpInvestigatePlan> findByTypeId(Long typeId);

    @Query("select p.id from BpInvestigatePlan p where p.checkPlanName like %:checkPlanName%")
    List<Long> findIdByCheckPlanNameContains(String checkPlanName);

    int countBySafeTableId(Long safeTableId);

    @Query(value ="select * from wwyt_bp_investigate_plan where hidden_danger_content_id in ?1 and initiate_status!='禁用' and delete_flag = '0' ORDER BY id asc ",nativeQuery = true)
    List<BpInvestigatePlan> findByHiddenDangerContentIds(List<Long> hiddenDangerContentIds);

    @Query(value ="select id from wwyt_bp_investigate_plan where hidden_danger_content_id in ?1 and delete_flag = '0' ORDER BY id asc ",nativeQuery = true)
    List<Long> findIdsByHiddenDangerContentIds(List<Long> hiddenDangerContentIds);

    @Modifying
    @Query(value = "update wwyt_bp_investigate_plan set initiate_status='禁用' where hidden_danger_content_id in ?1 ",nativeQuery = true)
    void updateInitiateStatusByIds(List<Long> ids);

    @Modifying
    @Query(value = "update wwyt_bp_investigate_plan set initiate_status = ?2,last_produce_date = null where hidden_danger_content_id in ?1 ",nativeQuery = true)
    Integer changeInitiateStatusByIds(List<Long> contentIds, String status);

    @Modifying
    @Query(value = "update wwyt_bp_investigate_plan set delete_flag='1' where hidden_danger_content_id in ?1 ",nativeQuery = true)
    void deleteByHiddenDangerContentIds (List<Long> ids);


    @Query(value = "SELECT * FROM wwyt_bp_investigate_plan WHERE source_type = 1 AND hidden_danger_content_id IS NOT NULL AND initiate_status = '启用'AND delete_flag = '0'",nativeQuery = true)
    List<BpInvestigatePlan> getEvaluationPlans();

    @Query(value = "SELECT count(1) " +
            "FROM wwyt_bp_investigate_plan plan " +
            "         inner join wwyt_bp_investigate_record record on plan.id = record.check_plan_id " +
            "    and plan.delete_flag = '0' and record.delete_flag = '0' " +
            "         inner join wwyt_bp_investigate_record_item item on record.id = item.record_id " +
            "    and item.delete_flag = '0' " +
            "         left join wwyt_workflow_task work " +
            "                   on item.id = work.ref_detail_id and work.ref_source = '隐患排查' and work.delete_flag = '0' " +
            "WHERE plan.source_type = 1 " +
            "  AND plan.delete_flag = '0' " +
            "  AND plan.id = ?1 " +
            "  AND ((item.repair_status = '未整改' or work.status = 0) or (item.wf_process_status is not null and item.wf_process_status not in ('完成','已终止')))" +
            "  AND record.check_result = '异常'",nativeQuery = true)
    long countUnRectifyRecord(Long planId);

    @Query(value = "SELECT count(1) " +
            "FROM wwyt_bp_investigate_plan plan " +
            "         inner join wwyt_bp_investigate_record_his record on plan.id = record.check_plan_id " +
            "    and plan.delete_flag = '0' and record.delete_flag = '0' " +
            "         inner join wwyt_bp_investigate_record_item_his item on record.id = item.record_id " +
            "    and item.delete_flag = '0' " +
            "         left join wwyt_workflow_task work " +
            "                   on item.id = work.ref_detail_id and work.ref_source = '隐患排查' and work.delete_flag = '0' " +
            "WHERE plan.source_type = 1 " +
            "  AND plan.delete_flag = '0' " +
            "  AND plan.id = ?1 " +
            "  AND ((item.repair_status = '未整改' or work.status = 0) or (item.wf_process_status is not null and item.wf_process_status not in ('完成','已终止'))) " +
            "  AND record.check_result = '异常'",nativeQuery = true)
    long countUnRectifyRecordHistory(Long planId);

    BpInvestigatePlan findFirstById(Long id);

    @Transactional
    @Modifying
    @Query(value = "update " +
            " wwyt_bp_investigate_task task " +
            "         inner join wwyt_bp_investigate_plan plan on plan.id = task.plan_id " +
            "    and task.delete_flag = '0' and plan.delete_flag = '0' and plan.source_type = 1 and task.status = '未完成' " +
            "         inner join wwyt_risk_hidden_danger_content hidden " +
            "                    on plan.hidden_danger_content_id = hidden.id " +
            "         inner join wwyt_risk_point_business_evaluation_detail detail " +
            "                    on hidden.detail_id = detail.id " +
            "         inner join wwyt_risk_point point on detail.risk_point_id = point.ID " +
            "set task.delete_flag = '1' " +
            "where plan.initiate_status = '禁用' " +
            "   or hidden.delete_flag = '1' " +
            "   or detail.delete_flag = '1' " +
            "   or point.DELETE_FLAG = '1'",nativeQuery = true)
    void cleanUselessTask();

    @Query(value = "select * from wwyt_bp_investigate_plan where id in ?1 ", nativeQuery = true)
    List<BpInvestigatePlan> findByIds(List<Long> ids);

    @Modifying
    @Query(value = "update wwyt_bp_investigate_plan set initiate_status=?2 where id in ?1 ",nativeQuery = true)
    void updateInitiateStatusByIds(List<Long> ids,String state);

    @Query(value = "select plan.* " +
            "from wwyt_bp_investigate_plan plan " +
            "         inner join wwyt_risk_hidden_danger_content hidden " +
            "                    on plan.hidden_danger_content_id = hidden.id and hidden.delete_flag = '0' and " +
            "                       plan.delete_flag = '0' and plan.source_type = 1 and plan.initiate_status = '启用' " +
            "                        and (plan.period_days != 0 or plan.period_days is null) " +
            "         inner join wwyt_risk_point_business_evaluation_detail detail " +
            "                    on hidden.detail_id = detail.id and detail.delete_flag = '0' " +
            "         inner join wwyt_risk_point point on detail.risk_point_id = point.ID and point.DELETE_FLAG = '0' where plan.id in ?1", nativeQuery = true)
    List<BpInvestigatePlan> findNewPlanToProduceById(List<Long> planIds);

    @Query(value = "select * from wwyt_bp_investigate_plan where point_evaluation_id = ?1 and source_type = 1 and initiate_status = '启用' and delete_flag = '0'", nativeQuery = true)
    List<BpInvestigatePlan> findByRiskPointEvaluationId(Long riskPointEvaluationId);

    @Query(value = "select count(id) from wwyt_bp_investigate_plan where risk_point_id = ?1 and point_evaluation_id = ?2 and source_type = 1 and delete_flag = '0'", nativeQuery = true)
    int countByRiskPointIdAndPointEvaluationId(Long riskPointId, Long pointEvaluationId);

    @Query(value = "select * from wwyt_bp_investigate_plan where check_depart_json like %?1% and source_type = 0 and delete_flag = '0'", nativeQuery = true)
    List<BpInvestigatePlan> findByDepartId(Long id);

}
